import { TCpId } from "@/models/types";
import { computed, ref, Ref } from "vue";
import { IDataset, IDataView, IPivotDataView } from "./types";
import { iterFilterExpr } from "./utils";
import { TComponentServices } from "@/services/componentServices";
import { TDbServices } from "../dbServices";
import { TSqlAnalyzeService } from "../sqlAnalyzeServices";
// import { replaceTableToFilterQuery } from "@/services/sqlAnalyzeServices";


export function create(name: string, sql: string, excludeLinkages: string[] = [], services: {
    component: TComponentServices,
    sqlAnalyze: TSqlAnalyzeService,
}): IDataView {
    const mLinkageDatasets = [] as IDataset[]
    const cp2FilterMap = new Map<TCpId, Ref<string>>()
    const excludeLinkagesSet = new Set(excludeLinkages)


    function toSqlWithFilters(requestorId: string, withOutFilters = false): Ref<string> {
        let sqlCopy = sql

        return computed(() => {
            mLinkageDatasets.forEach(ds => {
                const noFilters = excludeLinkagesSet.has(ds.name)
                const dsSql = ds.toSqlWithFilters(requestorId, noFilters)
                sqlCopy = services.sqlAnalyze.replaceTableToFilterQuery(sqlCopy, ds.name, dsSql.value)
            })

            if (withOutFilters) {
                return sqlCopy
            }

            const filters = Array.from(iterFilterExpr(cp2FilterMap, requestorId, [], services.component))
            const whereSql = filters.map(v => v.value).join(' and ')

            if (whereSql) {
                return `select * from (${sqlCopy}) where ${whereSql}`
            }

            return sqlCopy
        })
    }

    function addLinkageDataset(dataset: IDataset) {
        mLinkageDatasets.push(dataset)
    }

    function addFilter(updateId: string, expression: string): void {
        cp2FilterMap.get(updateId)!.value = expression
    }

    function removeFilters(updateId: string): void {
        cp2FilterMap.get(updateId)!.value = ''
    }

    function initFilter(updateId: string) {
        cp2FilterMap.set(updateId, ref(''))
    }

    function getAllFilters() {
        return Array.from(cp2FilterMap.entries()).map(([id, filter]) => {

            return {
                id, filter
            }
        })
    }

    return {
        typeName: 'dataView',
        name, sql,
        addLinkageDataset,
        toSqlWithFilters,
        addFilter,
        removeFilters,
        initFilter,
        cp2FilterMap,
        getAllFilters
    }

}


type TPivotOptions = {
    row: string
    column: string
    cell: string
    agg: string
    excludeRowFields: boolean
}

const mAggPat = /\$\{\}/

export function createPivotDataView(name: string, sourceDatasetName: string,
    pivotOptions: TPivotOptions,
    excludeLinkages: string[] = [], services: {
        component: TComponentServices,
        db: TDbServices,
    }): IPivotDataView {

    const mDv = create(name, '', excludeLinkages, services)
    let mSourceDataset: IDataset | null = null
    let aggFunc = pivotOptions.agg

    // 'min' to 'min(${})'
    if (!aggFunc.match(mAggPat)) {
        aggFunc = `${aggFunc}(\${})`
    }

    function toSqlWithFilters(requestorId: string): Ref<string> {
        return computed(() => {
            if (mSourceDataset === null) {
                throw new Error("pivot dataset not found source");
            }

            const sourceSql = mSourceDataset.toSqlWithFilters(requestorId)

            function createPivotSql() {
                const tempTable = services.db.query2tempory(`temp_${name}`, sourceSql.value)
                const query = services.db.queryAll(`select distinct ${pivotOptions.column} as value from ${tempTable}`)
                const colFileds = query.rows.map(r => r['value'])

                const colsSql = colFileds.map(f => {
                    const sqlValue = services.db.parse2sqlValueBaseFieldType(tempTable, pivotOptions.column, f)
                    const filterCol = `${pivotOptions.cell}) filter (where ${pivotOptions.column} = ${sqlValue}`
                    const aggSql = aggFunc.replace(mAggPat, filterCol)
                    return `${aggSql} as "${f}"`
                }).join(',')

                if (pivotOptions.excludeRowFields) {
                    return `select  ${colsSql} from ${tempTable} group by ${pivotOptions.row} order by ${pivotOptions.row}`
                }

                return `select ${pivotOptions.row} , ${colsSql} from ${tempTable} group by ${pivotOptions.row} order by ${pivotOptions.row}`
            }

            const pivotSql = createPivotSql()


            const filters = Array.from(iterFilterExpr(mDv.cp2FilterMap, requestorId, [], services.component))
            const whereSql = filters.map(v => v.value).join(' and ')

            if (whereSql) {
                return `select * from (${pivotSql}) where ${whereSql}`
            }

            return pivotSql
        })
    }

    function addLinkageDataset(dataset: IDataset) {
        mDv.addLinkageDataset(dataset)
        mSourceDataset = dataset
    }

    return {
        typeName: 'pivot-dataView',
        sourceDatasetName,
        name,
        toSqlWithFilters,
        addLinkageDataset,
        addFilter: mDv.addFilter,
        removeFilters: mDv.removeFilters,
        initFilter: mDv.initFilter,

    }

}